<?php

require_once('Vega/Dao/Table.php');

class Bbservice_CommonDao extends Vega_Dao_Table
{
    public function getUserName($userId)
    {
		$userId = $this->_db->valueQuote($userId);
        
        $sql = "SELECT name FROM user WHERE id = $userId";
        $this->_db->query($sql);
		
		if ($row = $this->_db->fetchRow()) {
			return $row['name'];
		}
		else 
			return null;
    }
	
    public function getUserDetail($userId)
    {
		$userId = $this->_db->valueQuote($userId);
        
        $sql = "SELECT id, name, image_url FROM user WHERE id = $userId";
        $this->_db->query($sql);
		
		if ($row = $this->_db->fetchRow()) {
			return $row;
		}
		else 
			return null;
    }
	
	public function updateUserName($userId, $userName) {
		$userId = $this->_db->valueQuote($userId);
		$userName = $this->_db->valueQuote($userName);
		
        $sql = "UPDATE user SET name=$userName WHERE id=$userId";
        $result = $this->_db->query($sql);
		
		return $result;
	}
	
	public function addUser($userId, $userName) {
		$userId = $this->_db->valueQuote($userId);
		$userName = $this->_db->valueQuote($userName);
		
        $sql = "INSERT INTO user SET id=$userId, name=$userName";
        $result = $this->_db->query($sql);
		
		return $result;
	}	
	
	public function removeFarms($userId) {
        $sql = "DELETE FROM farm WHERE user_id=$userId";
        $result = $this->_db->query($sql);
		
		return $result;		
	}

	public function removeFarmEvents($farmListStr) {
        $sql = "DELETE FROM event WHERE farm_id in ($farmListStr)";
        $result = $this->_db->query($sql);
		
		return $result;		
	}
	
	public function addFarmsByArray($farmList) {
		if (count($farmList)<1) return ;

		$values = '';
		foreach ($farmList as $f) {
			
			$square_id 			= $this->_db->valueQuote($f['square_id']);
			$user_id			= $this->_db->valueQuote($f['user_id']);
			$plant_id 			= $this->_db->valueQuote($f['plant_id']);
			$plant_time 		= $this->_db->valueQuote(date('Y-m-d H:i:s', $f['plant_time']));
			$last_harvert_time 	= $this->_db->valueQuote(date('Y-m-d H:i:s', $f['last_harvert_time']));
			$gen 				= $this->_db->valueQuote($f['gen']);
			$is_fertilised 		= $this->_db->valueQuote($f['is_fertilised']);
			$remainUnit 		= $this->_db->valueQuote($f['remain_unit']);
			
			if ($values != '') $values .= ',';
			$values .= "($square_id, $user_id, $plant_id, $plant_time, $gen, $is_fertilised, $last_harvert_time, $remainUnit)";
		}
		
		$sql = "INSERT INTO farm (id, user_id, plant_id, plant_time, gen, is_fertilised, last_harvert_time, remain_unit) VALUES $values ";
		$result = $this->_db->query($sql);
		
		return $result;
	}

	public function addEventsByArray($eventList) {
		if (count($eventList)<1) return ;

		$values = '';
		foreach ($eventList as $e) {
			
			$farm_id 	= $this->_db->valueQuote($e['farm_id']);
			$type_id	= $this->_db->valueQuote($e['type_id']);
			$time 		= $this->_db->valueQuote(date('Y-m-d H:i:s', $e['time']));
			$param 		= $this->_db->valueQuote($e['param']);
			
			if ($values != '') $values .= ',';
			$values .= "($farm_id, $type_id, $time, $param)";
		}
		
		$sql = "INSERT INTO event (farm_id, type_id, time, param) VALUES $values ";
		$result = $this->_db->query($sql);
		
		return $result;
	}
	
	public function addPlantsByArray($plantList) {
	
		if (count($plantList)<1) return ;

		$sql = "DELETE FROM plant";
		$result = $this->_db->query($sql);

		$values = '';

		foreach ($plantList as $p) {
			$plant_id 	= $this->_db->valueQuote($p->id);
			$name 		= $this->_db->valueQuote($p->n);
			$grow_time 	= $this->_db->valueQuote($p->gt);
			$gen 		= $this->_db->valueQuote($p->ng);
			$regrow_time = ($p->ng>1)?floor($p->gt/2.5):0;
			$unit 		= $this->_db->valueQuote($p->gu);
			$price 		= $this->_db->valueQuote($p->up);

			if ($values != '') $values .= ',';
			$values .= "($plant_id, $name, $grow_time, $regrow_time, $gen, $unit, $price)";
			
		}
		$sql = "INSERT INTO plant (id, name, grow_time, regrow_time, gen, unit, price) VALUES $values ";
		$result = $this->_db->query($sql);
		
		return $result;
	}
	
	public function addUsersByArray($userList) {
		foreach ($userList as $u) {
			$user_id 	= $this->_db->valueQuote($u->id);
			$name 		= $this->_db->valueQuote($u->fn . ' ' . $u->ln);
			$image_url 	= $this->_db->valueQuote($u->iu);
			$xp 		= $this->_db->valueQuote($u->xp);

			$values = "($user_id, $name, $image_url, $xp)";
			$sql = "INSERT INTO user (id, name, image_url, xp) VALUES $values ".
				"ON DUPLICATE KEY UPDATE name=$name, image_url=$image_url, xp=$xp";
			
			$this->_db->query($sql);
		}
		
		return true;
	}
	
	public function recalculateHarvertTime() {
		//calculate harvert time base on generation plant.grow time and plant.regrow_time
		$sql = 'UPDATE farm AS f INNER JOIN plant AS p ON f.plant_id=p.id SET f.harvert_time = DATE_ADD(f.last_harvert_time, INTERVAL IF(f.gen<2,p.grow_time,p.regrow_time) SECOND)';
		$this->_db->query($sql);
		
		//manipulate harvert time by fertilised effect
		$sql = 'UPDATE event AS e INNER JOIN farm as f ON e.farm_id=f.id INNER JOIN fertilised AS ft ON e.param=ft.id SET f.harvert_time=DATE_SUB(f.harvert_time,INTERVAL ft.time_sec SECOND) WHERE e.type_id=10 AND (e.time BETWEEN f.last_harvert_time AND f.harvert_time)';
		$this->_db->query($sql);
	}
	
	public function getHarvertData($params=null, $flags = 0) {
	
		if ($params == null) $params = Array();
		
		$strOrder = 'ORDER BY f.harvert_time';
		
		$strFilter = '';
		if ($flags & IGNORE_PAST) $strFilter .= ' AND f.harvert_time > now() ';
		
		if ($flags & PAST_ONLY) {
			$strFilter .= ' AND f.harvert_time < now() AND f.remain_unit > 0';
			$strOrder .= ' DESC';
		}
		
		if (isset($params['user_id'])) {
			$userId = $this->_db->valueQuote($params['user_id']);
			$strFilter .= ' AND u.id='.$userId;
		}

		$items = null;
		if (isset($params['limit'])) $items= intval($params['limit']);
		
		$limit = '';
		if ($items) $limit = "LIMIT $items";
		
		$sql = "SELECT 
					u.id as user_id, 
					u.name AS user_name, 
					u.image_url,
					p.name AS plant,
					timediff(f.harvert_time,now()) AS remain,
					f.harvert_time AS harvert_time,
					f.gen AS gen,
					p.price AS price,
					(p.price * p.unit) AS value,
					f.plant_time AS plant_time,
					f.last_harvert_time AS last_harvert_time 
				FROM ((farm f JOIN user u ON((f.user_id = u.id))) JOIN plant p ON((f.plant_id = p.id))) 
				WHERE 1 $strFilter
				$strOrder
				$limit";
		$this->_db->query($sql);
		
		return $this->_db->fetchRowset();
	}
	
	public function getUsersList() {
		$sql = "SELECT * FROM user ORDER BY xp DESC";
		$this->_db->query($sql);
		return $this->_db->fetchRowset();
	}
}